/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aaron.platform.service;

import com.aaron.platform.dao.IProjectDao;
import com.aaron.platform.entity.*;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.PageOrientation;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.log4j.Logger;

/**
 * Excel表格操作工具
 * @author chenzhuohua
 */
public class ExcelTools implements IExcelTools {

    private static Logger logger = Logger.getLogger(ExcelTools.class);

    private IProjectDao projectDao;

    /** 模板文件的目录 */
    private final static String CONFIG_PATH = "WEB-INF";
    /** 模板文件名 */
    private final static String ADJUST_FILE_NAME = "adjust.xls";

    public void setProjectDao(IProjectDao projectDao) {
        this.projectDao = projectDao;
    }

    public void outputAdjustTable(List<GczxPaymentInfo> paymentInfos) throws IOException, BiffException, WriteException, InterruptedException {

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        response.reset();
        response.setContentType("application/msexcel"); // 设置文件类型
        response.setHeader("Content-disposition", "attachment; filename="
                + "adjust.xls");
        OutputStream os = response.getOutputStream();

        // 创建调差工作簿
        WritableWorkbook workbook = genWorkbook(os);
        for (GczxPaymentInfo paymentInfo : paymentInfos) {
            genSheet(workbook, paymentInfo.getPayDetails());
        }

        // 删除模板
//        int defaultIndex = 0;
//        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
//            if (workbook.getSheet(i).getName().equals("model")) {
//                defaultIndex = i;
//            }
//        }
//
//        if (workbook.getNumberOfSheets() > 1) {
//           workbook.removeSheet(defaultIndex);
//        }
        if (workbook.getNumberOfSheets() == 0) {
           workbook.createSheet("default", 0);
        }

        workbook.write();
        workbook.close();

        FacesContext.getCurrentInstance().responseComplete();
        os.close();
    }

    /**
     * 产生一张调差工作表
     * @param payDetails 订单付款明细
     * @return
     */
    private void genSheet(WritableWorkbook wb, List<GczxPayDetail> payDetails) throws WriteException {
        
        // 列表空则返回
        if (payDetails == null || payDetails.isEmpty()) {
            logger.info("there is no order list to adjus.");
            return;
        }

        // 拷贝一个sheet
        int newSheetId = wb.getNumberOfSheets();
        //wb.copySheet("model", "default", newSheetId);
        WritableSheet newSheet = wb.createSheet("default", newSheetId);

        // 设置各列宽度
        newSheet.setColumnView(0, 13);
        newSheet.setColumnView(1, 25);
        newSheet.setColumnView(2, 20);
        newSheet.setColumnView(3, 25);
        newSheet.setColumnView(4, 15);
        newSheet.setColumnView(5, 15);
        newSheet.setColumnView(6, 15);
        newSheet.setColumnView(7, 15);

        // 设置各行高度
        newSheet.setRowView(0, 700, false);

        // 合并单元格
        newSheet.mergeCells(0, 0, 7, 0);
        WritableFont titleFont = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD);
        WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
        titleFormat.setAlignment(Alignment.CENTRE);
        titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        titleFormat.setBorder(Border.NONE, BorderLineStyle.THIN, Colour.BLACK);

        Label TitleLabel = new Label(0, 0, "订单与实际结算差异调整清单", titleFormat);
        newSheet.addCell(TitleLabel);
        
        // 设置单元格字体和格式（边框及对齐）
        WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD);
        WritableCellFormat format = new WritableCellFormat(font);
        format.setAlignment(Alignment.LEFT);
        format.setVerticalAlignment(VerticalAlignment.CENTRE);
        format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

        NumberFormat nf = new NumberFormat("#.00");
        WritableCellFormat numberFormat = new WritableCellFormat(nf);
        numberFormat.setAlignment(Alignment.LEFT);
        numberFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        numberFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

        //设置表头
        Label projectCodeTitle = new Label(0, 1, "项目号", format);
        newSheet.addCell(projectCodeTitle);
        Label projectNameTitle = new Label(1, 1, "项目名", format);
        newSheet.addCell(projectNameTitle);
        Label taskCodeTitle = new Label(2, 1, "任务号", format);
        newSheet.addCell(taskCodeTitle);
        Label taskNameTitle = new Label(3, 1, "任务名", format);
        newSheet.addCell(taskNameTitle);
        Label orderCodeTitle = new Label(4, 1, "订单号", format);
        newSheet.addCell(orderCodeTitle);
        Label orderValueTitle = new Label(5, 1, "订单价", format);
        newSheet.addCell(orderValueTitle);
        Label auditedValueTitle = new Label(6, 1, "审定价", format);
        newSheet.addCell(auditedValueTitle);
        Label adjustValueTitle = new Label(7, 1, "调整额", format);
        newSheet.addCell(adjustValueTitle);

        newSheet.setRowView(1, 402, false);

        // 插入一行，填写数据
        int row = 2;
        for (GczxPayDetail payDetail : payDetails) {
            newSheet.insertRow(row);
            newSheet.setRowView(row, 402, false);
            Label projectCodeLabel = new Label(0, row, payDetail.getOrderInfo().getProjectCode(), format);
            newSheet.addCell(projectCodeLabel);
            Label projectNameLabel = new Label(1, row, getProjectName(payDetail.getOrderInfo().getProjectCode()), format);
            newSheet.addCell(projectNameLabel);
            Label taskCodeLabel = new Label(2, row, payDetail.getOrderInfo().getTaskCode(), format);
            newSheet.addCell(taskCodeLabel);
            Label taskNameLabel = new Label(3, row, payDetail.getOrderInfo().getTaskName(), format);
            newSheet.addCell(taskNameLabel);
            Label orderCodeLabel = new Label(4, row, payDetail.getOrderInfo().getOrderCode(), format);
            newSheet.addCell(orderCodeLabel);
            Number orderValueLabel = new Number(5, row, payDetail.getOrderInfo().getOrderValue(), numberFormat);
            newSheet.addCell(orderValueLabel);
            Number auditedValueLabel = new Number(6, row, payDetail.getOrderInfo().getAuditedValue(), numberFormat);
            newSheet.addCell(auditedValueLabel);
            Number adjustValueLabel = new Number(7, row, 
                    payDetail.getOrderInfo().getAuditedValue() - payDetail.getOrderInfo().getOrderValue(), numberFormat);
            newSheet.addCell(adjustValueLabel);
            row++;
        }

        // 合计单元格
        newSheet.setRowView(row, 402, false);

        newSheet.mergeCells(0, row, 4, row);
        Label totalTitle = new Label(0, row, "合计", format);
        newSheet.addCell(totalTitle);

        // 订单价公式
        StringBuilder sb = new StringBuilder();
        sb.append("SUM(F3:F").append(row);
        Formula orderValueTotal = new Formula(5, row, sb.toString(), numberFormat);
        newSheet.addCell(orderValueTotal);

        // 审定价公式
        sb.delete(0, sb.length());
        sb.append("SUM(G3:G").append(row);
        Formula auditedValueTotal = new Formula(6, row, sb.toString(), numberFormat);
        newSheet.addCell(auditedValueTotal);

        // 调差额公式
        sb.delete(0, sb.length());
        sb.append("SUM(H3:H").append(row);
        Formula adjustValueTotal = new Formula(7, row, sb.toString(), numberFormat);
        newSheet.addCell(adjustValueTotal);

        // 增加空白行
        row++;
        newSheet.setRowView(row, 200, false);

        // 制表行
        row++;
        newSheet.setRowView(row, 402, false);

        // 设置制表人行格式（边框及对齐）
        WritableFont makerFont = new WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD);
        WritableCellFormat makerFormat = new WritableCellFormat(makerFont);
        makerFormat.setAlignment(Alignment.LEFT);
        makerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        makerFormat.setBorder(Border.NONE, BorderLineStyle.NONE);
        
        sb.delete(0, sb.length());
        sb.append("制表人：");
        Label makerLabel = new Label(4, row, sb.toString(), makerFormat);
        newSheet.addCell(makerLabel);

        sb.delete(0, sb.length());
        sb.append("日期：");
        Label dateTitleLabel = new Label(6, row, sb.toString(), makerFormat);
        newSheet.addCell(dateTitleLabel);

        Date currentDate = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Label dateLabel = new Label(7, row, sdf.format(currentDate), makerFormat);
        newSheet.addCell(dateLabel);

        newSheet.setName(payDetails.get(0).getOrderInfo().getTaskName());

        // 打印设置
        newSheet.setPageSetup(PageOrientation.LANDSCAPE);
        newSheet.getSettings().setLeftMargin(0.35d);
        newSheet.getSettings().setRightMargin(0.35d);
    }
    
    public void outputLxkrProjectTable(List<ZdwLxkrProjectInfo> lxkrProjectInfos) throws IOException, BiffException, WriteException, InterruptedException {

        HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        response.reset();
        response.setContentType("application/msexcel"); // 设置文件类型
        response.setHeader("Content-disposition", "attachment; filename="
                + "zdw_lxkr_project.xls");
        OutputStream os = response.getOutputStream();

        // 创建调差工作簿
        WritableWorkbook workbook = genWorkbook(os);
        genLxkrProjectSheet(workbook, lxkrProjectInfos);
 

        if (workbook.getNumberOfSheets() == 0) {
           workbook.createSheet("projects", 0);
        }

        workbook.write();
        workbook.close();

        FacesContext.getCurrentInstance().responseComplete();
        os.close();
    }
    
    private void genLxkrProjectSheet(WritableWorkbook wb, List<ZdwLxkrProjectInfo> lxkrProjectInfos) throws WriteException {
        
        // 列表空则返回
        if (lxkrProjectInfos == null || lxkrProjectInfos.isEmpty()) {
            logger.info("there is no lxkrProject list to generate.");
            return;
        }
        
        // 拷贝一个sheet
        int newSheetId = wb.getNumberOfSheets();
        // 新建一张工程汇总表
        WritableSheet newSheet = wb.createSheet("工程汇总表", newSheetId);
        // 新建一张材料明细表
        WritableSheet materSheet = wb.createSheet("材料明细表", newSheetId + 1);

        // 设置汇总表各列宽度
        newSheet.setColumnView(0, 7); // 区域
        newSheet.setColumnView(1, 30); // 驻地网小区名称
        newSheet.setColumnView(2, 18); // 原工程覆盖类型
        newSheet.setColumnView(3, 13); // 扩容还是覆盖
        newSheet.setColumnView(4, 15); // 扩容点DP盒数量
        newSheet.setColumnView(5, 12); // DP盒数量
        newSheet.setColumnView(6, 12); // 分光器数量
        newSheet.setColumnView(7, 12); // 光交数量
        newSheet.setColumnView(8, 15); // 光缆数量（米）
        newSheet.setColumnView(9, 20); // 扩容方案描述
        newSheet.setColumnView(10, 15); // 扩容户数
        newSheet.setColumnView(11, 20); // 预计投资（万元）
        newSheet.setColumnView(12, 20); // 提出需求日期
        newSheet.setColumnView(13, 20); // 市场要求完成日期
        newSheet.setColumnView(14, 20); // 实际扩容开工时间
        newSheet.setColumnView(15, 20); // 实际扩容完工时间
        newSheet.setColumnView(16, 10); // 联系人
        newSheet.setColumnView(17, 20); // 电话
        newSheet.setColumnView(18, 30); // 施工单位
        newSheet.setColumnView(19, 10); // 录入者
        
        
        // 设置材料表各列宽度
        materSheet.setColumnView(0, 35); // 驻地网小区名称
        materSheet.setColumnView(1, 15); // 材料类型
        materSheet.setColumnView(2, 15); // 材料型号
        materSheet.setColumnView(3, 10); // 材料单位
        materSheet.setColumnView(4, 10); // 材料数量

        // 设置各行高度
        newSheet.setRowView(0, 402, false);
        newSheet.setRowView(1, 402, false);
        materSheet.setRowView(0, 402, false);
        
        // 合并单元格
        newSheet.mergeCells(0, 0, 0, 1);
        newSheet.mergeCells(1, 0, 1, 1);
        newSheet.mergeCells(2, 0, 2, 1);
        newSheet.mergeCells(3, 0, 3, 1);
        newSheet.mergeCells(4, 0, 4, 1);
        newSheet.mergeCells(5, 0, 8, 0);
        newSheet.mergeCells(9, 0, 9, 1);
        newSheet.mergeCells(10, 0, 10, 1);
        newSheet.mergeCells(11, 0, 11, 1);
        newSheet.mergeCells(12, 0, 12, 1);
        newSheet.mergeCells(13, 0, 13, 1);
        newSheet.mergeCells(14, 0, 14, 1);
        newSheet.mergeCells(15, 0, 15, 1);
        newSheet.mergeCells(16, 0, 16, 1);
        newSheet.mergeCells(17, 0, 17, 1);
        newSheet.mergeCells(18, 0, 18, 1);
        newSheet.mergeCells(19, 0, 19, 1);

        // 设置标题
        WritableFont titleFont = new WritableFont(WritableFont.TIMES, 11, WritableFont.NO_BOLD);
        WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
        titleFormat.setAlignment(Alignment.LEFT);
        titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        titleFormat.setBackground(Colour.GRAY_25);
        
        // 设置单元格字体和格式（边框及对齐）
        WritableFont font = new WritableFont(WritableFont.TIMES, 11, WritableFont.NO_BOLD);
        WritableCellFormat format = new WritableCellFormat(font);
        format.setAlignment(Alignment.LEFT);
        format.setVerticalAlignment(VerticalAlignment.CENTRE);
        format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

        NumberFormat nf = new NumberFormat("#.00");
        WritableCellFormat numberFormat = new WritableCellFormat(nf);
        numberFormat.setAlignment(Alignment.RIGHT);
        numberFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        numberFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        
        NumberFormat inf = new NumberFormat("#");
        WritableCellFormat integerNumberFormat = new WritableCellFormat(inf);
        integerNumberFormat.setAlignment(Alignment.RIGHT);
        integerNumberFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        integerNumberFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        //设置表头
        Label domainTitle = new Label(0, 0, "区域", titleFormat);
        newSheet.addCell(domainTitle);
        Label residenceNameTitle = new Label(1, 0, "驻地网小区名称", titleFormat);
        newSheet.addCell(residenceNameTitle);
        Label coverTypeTitle = new Label(2, 0, "原工程覆盖类型", titleFormat);
        newSheet.addCell(coverTypeTitle);
        Label advanceTypeTitle = new Label(3, 0, "扩容还是覆盖", titleFormat);
        newSheet.addCell(advanceTypeTitle);
        Label oldDpQuantityTitle = new Label(4, 0, "扩容点DP盒数量", titleFormat);
        newSheet.addCell(oldDpQuantityTitle);
        
        Label expansionScopeTitle = new Label(5, 0, "扩容规模", titleFormat);
        newSheet.addCell(expansionScopeTitle);
        Label addedDpQuantityTitle = new Label(5, 1, "DP盒数量", titleFormat);
        newSheet.addCell(addedDpQuantityTitle);
        Label addedFgqQuantityTitle = new Label(6, 1, "分光器数量", titleFormat);
        newSheet.addCell(addedFgqQuantityTitle);
        Label addedGjQuantityTitle = new Label(7, 1, "光交数量", titleFormat);
        newSheet.addCell(addedGjQuantityTitle);
        Label addedGlQuantityTitle = new Label(8, 1, "光缆数量（米）", titleFormat);
        newSheet.addCell(addedGlQuantityTitle);
        
        
        Label expansionScopeDesTitle = new Label(9, 0, "扩容方案描述", titleFormat);
        newSheet.addCell(expansionScopeDesTitle);
        Label expansionQuantityTitle = new Label(10, 0, "扩容户数", titleFormat);
        newSheet.addCell(expansionQuantityTitle);
        Label estimatedInvestTitle = new Label(11, 0, "预计投资（万元）", titleFormat);
        newSheet.addCell(estimatedInvestTitle);
        Label toRequireDateTitle = new Label(12, 0, "提出需求日期", titleFormat);
        newSheet.addCell(toRequireDateTitle);
        Label toDoneDateTitle = new Label(13, 0, "市场要求完成日期", titleFormat);
        newSheet.addCell(toDoneDateTitle);
        Label startDateTitle = new Label(14, 0, "实际扩容开工时间", titleFormat);
        newSheet.addCell(startDateTitle);
        Label doneDateTitle = new Label(15, 0, "实际扩容完工时间", titleFormat);
        newSheet.addCell(doneDateTitle);
        Label contactNameTitle = new Label(16, 0, "联系人", titleFormat);
        newSheet.addCell(contactNameTitle);
        Label contactPhoneTitle = new Label(17, 0, "电话", titleFormat);
        newSheet.addCell(contactPhoneTitle);
        Label constructEnterpriseTitle = new Label(18, 0, "施工单位", titleFormat);
        newSheet.addCell(constructEnterpriseTitle);
        Label createUserTitle = new Label(19, 0, "录入者", titleFormat);
        newSheet.addCell(createUserTitle);
        
        Label residenceName2Title = new Label(0, 0, "驻地网小区名称", titleFormat);
        materSheet.addCell(residenceName2Title);
        Label materTypeTitle = new Label(1, 0, "类型", titleFormat);
        materSheet.addCell(materTypeTitle);
        Label materDetailTitle = new Label(2, 0, "型号", titleFormat);
        materSheet.addCell(materDetailTitle);
        Label materUnitTitle = new Label(3, 0, "单位", titleFormat);
        materSheet.addCell(materUnitTitle);
        Label materQuantityTitle = new Label(4, 0, "数量", titleFormat);
        materSheet.addCell(materQuantityTitle);

        newSheet.setRowView(2, 402, false);
        materSheet.setRowView(1, 402, false);

        // 插入一行，填写数据
        int row = 2;
        int materRow = 1;
        
        for (ZdwLxkrProjectInfo lxkrProjectInfo : lxkrProjectInfos) {
            newSheet.insertRow(row);
            newSheet.setRowView(row, 402, false);
            
            Label domainLabel = new Label(0, row, lxkrProjectInfo.getDomainInfo().getDomainName(), format);
            newSheet.addCell(domainLabel);
            Label residenceNameLabel = new Label(1, row, lxkrProjectInfo.getResidenceName(), format);
            newSheet.addCell(residenceNameLabel);
            Label coverTypeLabel = new Label(2, row, lxkrProjectInfo.getDisplayCoverType(), format);
            newSheet.addCell(coverTypeLabel);
            Label advanceTypeLabel = new Label(3, row, lxkrProjectInfo.getDisplayAdvanceType(), format);
            newSheet.addCell(advanceTypeLabel);
            Label dpNumberLabel = new Label(4, row, String.valueOf(lxkrProjectInfo.getDpQuantity()), integerNumberFormat);
            newSheet.addCell(dpNumberLabel);
            
            int dpQuantiy = 0;
            int fgqQuantiy = 0;
            int gjQuantity = 0;
            int glQuantity = 0;
            List<ZdwLxkrProjectMaterial> materials = lxkrProjectInfo.getLxkrProjectMaterials();
            for (ZdwLxkrProjectMaterial material : materials) {
                if (material.getMaterType().equals("DP盒")) {
                    dpQuantiy += material.getMaterQuantity();
                } else if (material.getMaterType().equals("分光器")) {
                    fgqQuantiy += material.getMaterQuantity(); 
                } else if (material.getMaterType().equals("光交")) {
                    gjQuantity += material.getMaterQuantity(); 
                } else if (material.getMaterType().equals("光缆")) {
                    glQuantity += material.getMaterQuantity(); 
                } 
                
                // 记录材料明细
                materSheet.insertRow(materRow);
                materSheet.setRowView(materRow, 402, false);
                Label materResiNameLabel = new Label(0, materRow, lxkrProjectInfo.getResidenceName(), format);
                materSheet.addCell(materResiNameLabel);
                Label materTypeLabel = new Label(1, materRow, material.getMaterType(), format);
                materSheet.addCell(materTypeLabel);
                Label materDetailLabel = new Label(2, materRow, material.getMmaterDetail(), format);
                materSheet.addCell(materDetailLabel);
                Label materUnitLabel = new Label(3, materRow, material.getMaterUnit(), integerNumberFormat);
                materSheet.addCell(materUnitLabel);
                Label materQuantityLabel = new Label(4, materRow, String.valueOf(material.getMaterQuantity()), format);
                materSheet.addCell(materQuantityLabel);
                materRow++;
            }
            Label dpQuantityLabel = new Label(5, row, String.valueOf(dpQuantiy), integerNumberFormat);
            newSheet.addCell(dpQuantityLabel);
            Label fgqQuantityLabel = new Label(6, row, String.valueOf(fgqQuantiy), integerNumberFormat);
            newSheet.addCell(fgqQuantityLabel);
            Label gjQuantityLabel = new Label(7, row, String.valueOf(gjQuantity), integerNumberFormat);
            newSheet.addCell(gjQuantityLabel);
            Label glQuantityLabel = new Label(8, row, String.valueOf(glQuantity), integerNumberFormat);
            newSheet.addCell(glQuantityLabel);
            
            Label expansionScopeLabel = new Label(9, row, lxkrProjectInfo.getExpansionScope(), format);
            newSheet.addCell(expansionScopeLabel);
            Label expansionQuantityLabel = new Label(10, row, String.valueOf(lxkrProjectInfo.getExpansionQuantity()), integerNumberFormat);
            newSheet.addCell(expansionQuantityLabel);
            Label estimatedInvestLabel = new Label(11, row, String.valueOf(lxkrProjectInfo.getEstimatedInvest()), numberFormat);
            newSheet.addCell(estimatedInvestLabel);
            Label toRequireDateLabel = new Label(12, row, sdf.format(lxkrProjectInfo.getToRequireDate()), format);
            newSheet.addCell(toRequireDateLabel);
            Label toDoneDateLabel = new Label(13, row, sdf.format(lxkrProjectInfo.getToDoneDate()), format);
            newSheet.addCell(toDoneDateLabel);
            Label startDateLabel = new Label(14, row, sdf.format(lxkrProjectInfo.getStartDate()), format);
            newSheet.addCell(startDateLabel);
            Label doneDateLabel = new Label(15, row, sdf.format(lxkrProjectInfo.getDoneDate()), format);
            newSheet.addCell(doneDateLabel);
            Label contactNameLabel = new Label(16, row, lxkrProjectInfo.getContactName(), format);
            newSheet.addCell(contactNameLabel);
            Label contactPhoneLabel = new Label(17, row, lxkrProjectInfo.getContactPhone(), format);
            newSheet.addCell(contactPhoneLabel);
            Label constructEnterpriseLabel = new Label(18, row, lxkrProjectInfo.getEnterpriseInfo().getEnterpriseName(), format);
            newSheet.addCell(constructEnterpriseLabel);
            Label createUserLabel = new Label(19, row, lxkrProjectInfo.getCreateUserName(), format);
            newSheet.addCell(createUserLabel);
            
            row++;
        }
    }

    /**
     * 根据模板获取工作簿
     * @param os 输出流，输出到HTTP流
     * @return 工作簿
     * @throws IOException
     * @throws BiffException
     */
    private WritableWorkbook genWorkbook(OutputStream os) throws IOException, BiffException {

        // 获取模板文件的路径
//        String classPath = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
//        String excelPath = classPath.substring(0,
//                classPath.indexOf(CONFIG_PATH) + 8);
//        String adjustXlsPath = excelPath + File.separator + ADJUST_FILE_NAME;
//
//        // 获取模板文件，得到工作簿
//        File agjustXls = new File(adjustXlsPath);
        //Workbook modelBook = Workbook.getWorkbook(agjustXls);
        WritableWorkbook wb = Workbook.createWorkbook(os);

        return wb;
    }

    /**
     * 根据项目获取项目名
     * @param projectCode 项目编号
     * @return 项目名称
     */
    private String getProjectName(String projectCode) {
        FgysProject project = projectDao.findProjectByCode(projectCode);
        if (project == null) {
            return "";
        }
        return project.getProjectName();
    }
}
